package com.jintian.smart.kernel.orm.sql;

import org.beetl.sql.clazz.kit.BeetlSQLException;
import org.beetl.sql.clazz.kit.StringKit;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.query.GroupBy;
import org.beetl.sql.core.query.OrderBy;
import org.beetl.sql.core.query.Query;
import org.beetl.sql.core.query.interfacer.StrongValue;

import java.util.*;

public class SqlCondition {
    public static final String AND = "AND";
    public static final String OR = "OR";
    public static final String WHERE = "WHERE";
    public static final String IN = "IN";
    public static final String NOT_IN = "NOT IN";
    public static final String BETWEEN = "BETWEEN";
    public static final String NOT_BETWEEN = "NOT BETWEEN";
    public SQLManager sqlManager;
    protected StringBuilder sql = null;
    protected List<Object> params = new ArrayList<>();
    protected Object startRow = null;
    long pageSize = -1;
    protected OrderBy orderBy = null;
    protected GroupBy groupBy = null;
    protected boolean distinct = false;

    protected String tableName;
    //提醒表名是虚拟表，需要表达式计算真实表名
    boolean asVirtual = false;
    boolean hasWhere = false;

    protected SqlCondition() {
    }

    protected void clear() {
        sql = null;
        params = new ArrayList<>();
        startRow = null;
        pageSize = -1;
        orderBy = null;
        groupBy = null;
        this.hasWhere = false;
    }

    /**
     * 获取字段 加上前后空格
     *
     * @param colName
     * @return
     */
    protected String getCol(String colName) {
        return " " + colName + " ";
    }

    /**
     * 拼接SQL
     *
     * @param sqlPart
     */
    public SqlCondition appendSql(String sqlPart) {
        if (this.sql == null) {
            this.sql = new StringBuilder();
        }
        sql.append(sqlPart);
        return this;
    }


    /**
     * 增加参数
     *
     * @param objects
     * @return
     */
    public SqlCondition addParam(Collection<?> objects) {
        params.addAll(objects);
        return this;
    }


    /**
     * 在头部增加参数
     *
     * @param objects
     * @return
     */
    public void addPreParam(List<Object> objects) {
        objects.addAll(params);
    }

    /**
     * 增加参数
     *
     * @param object
     * @return
     */
    public SqlCondition addParam(Object object) {
        params.add(object);
        return this;
    }

    protected void appendAndSql(String column, Object value, String opt) {
        appendSqlBase(column, value, opt, AND);
    }

    protected void appendOrSql(String column, Object value, String opt) {
        appendSqlBase(column, value, opt, OR);
    }

//	protected void appendSqlBase(String column, Object value, String opt, String link) {
//		//判断是否有效的变量
//		if (!isValidateValue(value)) {
//			return;
//		}
//		value = getRealValue(value);
//		if (!hasWhere()) {
//			link = WHERE;
//			setWhere();
//		}
//		this.appendSql(link).appendSql(getCol(column)).appendSql(opt);
//		if (value != null) {
//			this.appendSql(" ? ");
//			this.addParam(value);
//		}
//	}

    protected void appendSqlBase(String column, Object value, String opt, String link) {
        // 判断是否有效的变量
        if (value instanceof StrongValue) {
            if (!((StrongValue) value).isEffective()) {
                return;
            }
            value = ((StrongValue) value).getValue();
        } else if (value instanceof Optional) {
            if (!((Optional) value).isPresent()) {
                return;
            }
            value = ((Optional) value).get();
        }
        if (value == null) {
            return;
        }
        if ((value instanceof String) && StringKit.isBlank(value.toString())) {
            return;
        }
        if (getSql().indexOf(WHERE) != 0) {
            link = WHERE;
            hasWhere = true;
        }
        this.appendSql(link).appendSql(getCol(column)).appendSql(opt);
        if (value != null) {
            this.appendSql(" ? ");
            if ((opt.equals("LIKE ") || opt.equals("NOT LIKE ")) && !value.toString().contains("%")) {
                value = "%" + value + "%";
            }
            this.addParam(value);
        }
    }

    protected boolean hasWhere() {
        return hasWhere;
    }

    protected void setWhere() {
        this.hasWhere = true;
    }

    protected void resetWhere() {
        this.hasWhere = false;
    }

    protected boolean isValidateValue(Object value) {
        if (value instanceof StrongValue) {
            if (((StrongValue) value).isEffective()) {
                return true;
            } else {
                return false;
            }

        } else if (value instanceof Optional) {
            if (((Optional) value).isPresent()) {
                return true;
            } else {
                return false;
            }

        }

        return true;
    }


    protected void appendInSql(String column, Object value, String opt, String link) {
        //判断是否有效的变量
        if (value instanceof StrongValue) {
            if (!((StrongValue) value).isEffective()) {
                return;
            }
            value = ((StrongValue) value).getValue();
        } else if (value instanceof Optional) {
            if (!((Optional) value).isPresent()) {
                return;
            }
            value = ((Optional) value).get();
        }

        if (!(value instanceof Collection)) {
            throw new IllegalArgumentException("期望参数是Collection子类");
        }

        if (!hasWhere()) {
            link = WHERE;
            setWhere();
        }
        this.appendSql(link).appendSql(getCol(column)).appendSql(opt).appendSql(" ( ");

        for (Object o : (Collection<?>) value) {
            this.appendSql(" ? ,");
            this.addParam(o);
        }
        this.getSql().deleteCharAt(this.getSql().length() - 1);
        this.appendSql(" ) ");
    }

    protected void appendBetweenSql(String column, String opt, String link, Object... value) {
        boolean firstMatch = isValidateValue(value[0]);
        boolean secondMatch = isValidateValue(value[1]);
        boolean isBetween = opt.equals(BETWEEN);
        if (!hasWhere()) {
            link = WHERE;
            setWhere();
        }

        if (firstMatch && secondMatch) {
            this.appendSql(link).appendSql(getCol(column)).appendSql(opt).appendSql(" ? AND ? ");
            this.addParam(getRealValue(value[0]));
            this.addParam(getRealValue(value[1]));
        } else if (firstMatch) {
            if (isBetween) {
                this.appendSql(link).appendSql(getCol(column)).appendSql(">=?");
                this.addParam(getRealValue(value[0]));
            } else {
                this.appendSql(link).appendSql(getCol(column)).appendSql("<?");
                this.addParam(getRealValue(value[0]));
            }

        } else if (secondMatch) {
            if (isBetween) {
                this.appendSql(link).appendSql(getCol(column)).appendSql("<=?");
                this.addParam(getRealValue(value[1]));
            } else {
                this.appendSql(link).appendSql(getCol(column)).appendSql(">?");
                this.addParam(getRealValue(value[1]));
            }

        } else {
            return;
        }

    }

    protected Object getRealValue(Object value) {
        if (value instanceof StrongValue) {

            return ((StrongValue) value).getValue();
        } else if (value instanceof Optional) {

            return ((Optional) value).get();
        } else {
            return value;
        }
    }


    public SqlCondition andEq(String column, Object value) {
        appendAndSql(column, value, "=");
        return this;
    }


    public SqlCondition andNotEq(String column, Object value) {
        appendAndSql(column, value, "<>");
        return this;
    }

    /**
     * 大于
     */

    public SqlCondition andGreat(String column, Object value) {
        appendAndSql(column, value, ">");
        return this;
    }

    /**
     * 大于等于
     */

    public SqlCondition andGreatEq(String column, Object value) {
        appendAndSql(column, value, ">=");
        return this;
    }

    /**
     * 小于
     */

    public SqlCondition andLess(String column, Object value) {
        appendAndSql(column, value, "<");
        return this;
    }

    /**
     * 小于等于
     */

    public SqlCondition andLessEq(String column, Object value) {
        appendAndSql(column, value, "<=");
        return this;
    }


    public SqlCondition andLike(String column, Object value) {
        appendAndSql(column, value, "LIKE ");
        return this;
    }


    public SqlCondition andNotLike(String column, Object value) {
        appendAndSql(column, value, "NOT LIKE ");
        return this;
    }


    public SqlCondition andIsNull(String column) {
        appendAndSql(column, null, "IS NULL ");
        return this;
    }


    public SqlCondition andIsNotNull(String column) {
        appendAndSql(column, null, "IS NOT NULL ");
        return this;
    }


    public SqlCondition andIn(String column, Collection<?> value) {
        appendInSql(column, value, IN, AND);
        return this;
    }


    public SqlCondition andIn(String column, StrongValue value) {
        appendInSql(column, value, IN, AND);
        return this;
    }


    public SqlCondition andIn(String column, Optional value) {
        appendInSql(column, value, IN, AND);
        return this;
    }


    public SqlCondition andNotIn(String column, Collection<?> value) {
        appendInSql(column, value, NOT_IN, AND);
        return this;
    }

    public SqlCondition andNotIn(String column, Query<?> query) {
        appendInSql(column, query, NOT_IN, AND);
        return this;
    }


    public SqlCondition andNotIn(String column, StrongValue value) {
        appendInSql(column, value, NOT_IN, AND);
        return this;
    }


    public SqlCondition andNotIn(String column, Optional value) {
        appendInSql(column, value, NOT_IN, AND);
        return this;
    }

    protected Object checkColValue(Object value) {
        if (value instanceof StrongValue) {
            if (!((StrongValue) value).isEffective()) {
                value = null;
            } else {
                value = ((StrongValue) value).getValue();
            }

        } else if (value instanceof Optional) {
            if (!((Optional) value).isPresent()) {
                value = null;
            }
            value = ((Optional) value).get();
        }
        return value;
    }


    public SqlCondition andBetween(String column, Object value1, Object value2) {
        value1 = this.checkColValue(value1);
        value2 = this.checkColValue(value2);
        if (value1 != null && value2 != null) {
            appendBetweenSql(column, BETWEEN, AND, value1, value2);
        } else if (value1 != null && value2 == null) {
            this.andGreatEq(column, value1);
        } else if (value1 == null && value2 != null) {
            this.andLessEq(column, value2);
        }
        return this;
    }


    public SqlCondition andNotBetween(String column, Object value1, Object value2) {
        value1 = this.checkColValue(value1);
        value2 = this.checkColValue(value2);
        if (value1 == null || value2 == null)
            throw new BeetlSQLException(BeetlSQLException.QUERY_CONDITION_ERROR, NOT_BETWEEN + "的2个参数不能为空");
        appendBetweenSql(column, NOT_BETWEEN, AND, value1, value2);
        return this;
    }


    public SqlCondition orEq(String column, Object value) {
        appendOrSql(column, value, "=");
        return this;
    }


    public SqlCondition orNotEq(String column, Object value) {
        appendOrSql(column, value, "<>");
        return this;
    }

    /**
     * 大于
     */

    public SqlCondition orGreat(String column, Object value) {
        appendOrSql(column, value, ">");
        return this;
    }

    /**
     * 大于等于
     */

    public SqlCondition orGreatEq(String column, Object value) {
        appendOrSql(column, value, ">=");
        return this;
    }

    /**
     * 小于
     */

    public SqlCondition orLess(String column, Object value) {
        appendOrSql(column, value, "<");
        return this;
    }

    /**
     * 小于等于
     */

    public SqlCondition orLessEq(String column, Object value) {
        appendOrSql(column, value, "<=");
        return this;
    }


    public SqlCondition orLike(String column, Object value) {
        appendOrSql(column, value, "LIKE ");
        return this;
    }


    public SqlCondition orNotLike(String column, Object value) {
        appendOrSql(column, value, "NOT LIKE ");
        return this;
    }


    public SqlCondition orIsNull(String column) {
        appendOrSql(column, null, "IS NULL ");
        return this;
    }


    public SqlCondition orIsNotNull(String column) {
        appendOrSql(column, null, "IS NOT NULL ");
        return this;
    }


    public SqlCondition orIn(String column, Collection<?> value) {
        appendInSql(column, value, IN, OR);
        return this;
    }


    public SqlCondition orIn(String column, StrongValue value) {
        appendInSql(column, value, IN, OR);
        return this;
    }


    public SqlCondition orIn(String column, Optional value) {
        appendInSql(column, value, IN, OR);
        return this;
    }


    public SqlCondition orNotIn(String column, Collection<?> value) {
        appendInSql(column, value, NOT_IN, OR);
        return this;
    }


    public SqlCondition orNotIn(String column, StrongValue value) {
        appendInSql(column, value, NOT_IN, OR);
        return this;
    }


    public SqlCondition orNotIn(String column, Optional value) {
        appendInSql(column, value, NOT_IN, OR);
        return this;
    }


    public SqlCondition orBetween(String column, Object value1, Object value2) {
        appendBetweenSql(column, BETWEEN, OR, value1, value2);
        return this;
    }


    public SqlCondition orNotBetween(String column, Object value1, Object value2) {
        appendBetweenSql(column, NOT_BETWEEN, OR, value1, value2);
        return this;
    }


    public SqlCondition and(SqlCondition condition) {
        return manyCondition(condition, AND);
    }


    public SqlCondition or(SqlCondition condition) {
        return manyCondition(condition, OR);
    }


    public SqlCondition and(String condition, Object[] params) {
        return manyCondition(condition, params, AND);
    }


    public SqlCondition or(String condition, Object[] params) {
        return manyCondition(condition, params, OR);
    }


    public SqlCondition asTable(String tableName) {
        this.tableName = tableName;
        return this;
    }


    public SqlCondition virtualTable() {
        asVirtual = true;
        return this;
    }

    private SqlCondition manyCondition(String condition, Object[] params, String link) {
        if (getSql().indexOf(WHERE) != 0) {
            link = WHERE;
        }
        appendSql(link).appendSql(" (").appendSql(condition).appendSql(")");
        addParam(Arrays.asList(params));
        return this;
    }

    private SqlCondition manyCondition(SqlCondition condition, String link) {
        if (condition.getSql() == null || "".equals(condition.getSql().toString())) {
            return this;
        }

        if (condition.hasWhere()) {
            //去除叠加条件中的WHERE
            int i = condition.getSql().indexOf(WHERE);
            if (i > -1) {
                condition.getSql().delete(i, i + 5);
            }
            condition.resetWhere();
        }

        if (!hasWhere()) {
            link = WHERE;
            setWhere();
        }

        appendSql(link).appendSql(" (").appendSql(condition.getSql().toString()).appendSql(") ");
        addParam(condition.getParams());
        return this;
    }


    public StringBuilder getSql() {
        if (this.sql == null) {
            this.sql = new StringBuilder();
        }
        return this.sql;
    }


    public void setSql(StringBuilder sql) {
        this.sql = sql;
    }


    public List<Object> getParams() {
        return params;
    }


    public SqlCondition distinct() {
        this.distinct = true;
        return this;
    }
}
